#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <time.h>
void get_time(char regdatetime[200])
{
    time_t tm1;
	struct tm *pt;
	tm1 = time(NULL);
	pt = localtime(&tm1);
	snprintf(regdatetime, 200, "%4d-%02d-%02d %02d:%02d:%02d 星期%d",
		pt->tm_year+1900, pt->tm_mon+1, pt->tm_mday,
		pt->tm_hour, pt->tm_min, pt->tm_sec, pt->tm_wday);
}


int main(void)
{
    int i, j;
    int ret;
    sqlite3 *pdb; //数据库句柄指针对象
    char *errmsg;
    //打开数据库
    ret = sqlite3_open("iot.db", &pdb);
    if (ret != SQLITE_OK) {
        /**
         * 不能用perror，可以用fprintf
         */
        fprintf(stderr, "sqlite3_open fial:%s\n", sqlite3_errmsg(pdb));
        exit(1);

    }
    //执行sql
    //建表
    char sql[BUFSIZ] = "create table if not exists users(uname varchar(20) primary key, pass varchar(20), workid int, regdatetime char[200]);";
    ret = sqlite3_exec(pdb, sql, NULL, NULL,&errmsg);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "sqlite3_exec fial:%s\n", errmsg);
        exit(1);

    }

    //循环输入5条命令
   
#if 0
    char uname[30];
    char pass[30];
    int workid;
    char regdatetime[200];
    for (i = 0; i < 5; i++) {
        printf("pls input uname pass workid:\n");
        scanf("%s%s%d", uname, pass, &workid);
        get_time(regdatetime);
        snprintf(sql, BUFSIZ, "insert into users values('%s', '%s', %d, '%s');", uname, pass, workid, regdatetime);
        printf("%s\n", sql);
        ret = sqlite3_exec(pdb, sql, NULL, NULL,&errmsg);
        if (ret != SQLITE_OK) {
            fprintf(stderr, "sqlite3_exec fial:%s\n", errmsg);
            exit(1);
        }
    }
#endif

#if 1
    //查询表
    char **result;
    int rows, columns;

    /**
     * result 查询结构放入这里，这里里面申请了堆内存，下面也要释放sqlite3_free_table
     */
    ret = sqlite3_get_table(pdb, "select * from users;", &result, &rows, &columns, &errmsg);
    
   //   ret = sqlite3_get_table(pdb, "select * from users where uname='rose' and pass='111';", &result, &rows, &columns, &errmsg);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "sqlite3_get_table fial:%s\n", errmsg);
        exit(1);

    }
    printf("row=%d col=%d\n", rows, columns);

    for(i = 0; i < rows+1; i++)	{						
        for(j=0; j < columns; j++)						
            printf("%s\t\t", result[i*columns+j]);					
        printf("\n");						
    }

    sqlite3_free_table(result);


#endif 
 
    //关闭数据库
    sqlite3_close(pdb);


    return 0;
}
